package cn.quevo.core.file.excel;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import cn.quevo.core.lang.StringUtils;


public class ExcelSimpleTools {

    /**
     * 解析Excel
     *
     * @param excel      excel文件
     * @param sheetIndex Sheet索引
     * @param firstLine  从第N行开始解析
     * @param cols       总列数
     * @return 解析完的数据集
     * @throws FileNotFoundException
     * @throws InvalidFormatException
     * @throws IOException
     * @author July july_sky@foxmail.com
     * @date 2017年10月30日 下午12:11:03
     */
    public static List<Map<String, String>> getSheetSimplate(File excel, int sheetIndex, int firstLine, int cols) throws FileNotFoundException, InvalidFormatException, IOException {
        return getSheetTitle(getWorkbook(excel), sheetIndex, firstLine, cols);
    }

    /**
     * 打开excel文档
     *
     * @param excel excel文档
     * @return
     * @author July july_sky@foxmail.com
     * @date 2018-03-09 15:47:56
     * @Copyright ©2015-2035 湘豫(北京)科技有限公司. All Rights Reserved.
     * @version 1.0
     */
    public static Workbook getWorkbook(File excel) throws FileNotFoundException, IOException, InvalidFormatException {
        if (excel.getName().toLowerCase().endsWith(".xls")) {
            try (Workbook work = new HSSFWorkbook(new FileInputStream(excel));) {
                return work;
            } catch (IOException e) {
            }
        }
        try (Workbook work = new XSSFWorkbook(excel);) {
            return work;
        } catch (IOException e) {
            throw e;
        }
    }

    /**
     * 打开excel文档(2013版)
     *
     * @param input
     * @return
     * @author July july_sky@foxmail.com
     * @date 2018-03-09 15:49:27
     * @Copyright ©2015-2035 湘豫(北京)科技有限公司. All Rights Reserved.
     * @version 1.0
     */
    public static Workbook getWorkbookXls(InputStream input) throws FileNotFoundException, IOException, InvalidFormatException {
        try (Workbook work = new HSSFWorkbook(input);) {
            return work;
        } catch (IOException e) {
            throw e;
        }
    }

    /**
     * 打开excel文档(2017版)
     *
     * @param input
     * @return
     * @author July july_sky@foxmail.com
     * @date 2018-03-09 15:49:37
     * @Copyright ©2015-2035 湘豫(北京)科技有限公司. All Rights Reserved.
     * @version 1.0
     */
    public static Workbook getWorkbookXlsx(InputStream input) throws FileNotFoundException, IOException, InvalidFormatException {
        try (Workbook work = new XSSFWorkbook(input);) {
            return work;
        } catch (IOException e) {
            throw e;
        }
    }

    /**
     * 获取Sheet内容
     *
     * @param book       Excel工作簿
     * @param sheetIndex Sheet索引
     * @param firstLine  起始行
     * @param cols       总列数
     * @return Sheet内容
     * @author July july_sky@foxmail.com
     * @date 2018-03-09 15:49:46
     * @Copyright ©2015-2035 湘豫(北京)科技有限公司. All Rights Reserved.
     * @version 1.0
     */
    public static List<Map<String, String>> getSheetTitle(Workbook book, int sheetIndex, int firstLine, int cols) {
        return getSheetTitle(book.getSheetAt(sheetIndex), firstLine, cols);
    }

    /**
     * 获取Sheet内容
     *
     * @param book      Excel工作簿
     * @param name      Sheet名称
     * @param firstLine 起始行
     * @param cols      总列数
     * @return Sheet内容
     * @author July july_sky@foxmail.com
     * @date 2018-03-09 15:49:54
     * @Copyright ©2015-2035 湘豫(北京)科技有限公司. All Rights Reserved.
     * @version 1.0
     */
    public static List<Map<String, String>> getSheetTitle(Workbook book, String name, int firstLine, int cols) {
        return getSheetTitle(book.getSheet(name), firstLine, cols);
    }

    /**
     * 获取Sheet内容
     *
     * @param book       Excel工作簿
     * @param sheetIndex Sheet索引
     * @param firstLine  起始行
     * @param cols       总列数
     * @return Sheet内容
     * @author July july_sky@foxmail.com
     * @date 2018-03-09 15:49:59
     * @Copyright ©2015-2035 湘豫(北京)科技有限公司. All Rights Reserved.
     * @version 1.0
     */
    public static List<Map<Integer, String>> getSheet(Workbook book, int sheetIndex, int firstLine, int cols) {
        return getSheet(book.getSheetAt(sheetIndex), firstLine, cols);
    }

    /**
     * 获取Sheet内容
     *
     * @param book      Excel工作簿
     * @param name      Sheet名称
     * @param firstLine 起始行
     * @param cols      总列数
     * @return Sheet内容
     * @author July july_sky@foxmail.com
     * @date 2018-03-09 15:50:04
     * @Copyright ©2015-2035 湘豫(北京)科技有限公司. All Rights Reserved.
     * @version 1.0
     */
    public static List<Map<Integer, String>> getSheet(Workbook book, String name, int firstLine, int cols) {
        return getSheet(book.getSheet(name), firstLine, cols);
    }

    /**
     * 根据Title解析
     *
     * @param sheet     Sheet
     * @param firstLine 起始行
     * @param cols      总列数
     * @return Sheet内容
     * @author July july_sky@foxmail.com
     * @date 2018-03-09 15:50:11
     * @Copyright ©2015-2035 湘豫(北京)科技有限公司. All Rights Reserved.
     * @version 1.0
     */
    public static List<Map<String, String>> getSheetTitle(Sheet sheet, int firstLine, int cols) {
        int rowCount = sheet.getLastRowNum();
        List<Map<String, String>> list = new ArrayList<>();
        if (rowCount <= firstLine + 1) {
            return list;
        }
        Row titleRow = sheet.getRow(firstLine);
        Map<Integer, String> columns = new LinkedHashMap<>();
        Map<String, String> rowMap = null;
        for (int col = 0; col < cols; col++) {
            if (StringUtils.isBlank(titleRow.getCell(col))) {
                break;
            }
            columns.put(col, titleRow.getCell(col).toString());
        }
        for (int row = firstLine + 1; row < rowCount; row++) {
            if (StringUtils.isBlank(sheet.getRow(row).getCell(0))) {
                break;
            }
            rowMap = new HashMap<>();
            for (int col = 0; col < cols; col++) {
                if (StringUtils.isBlank(columns.get(col))) {
                    continue;
                }
                rowMap.put(columns.get(col), StringUtils.isBlank(sheet.getRow(row).getCell(col)) ? "" : sheet.getRow(row).getCell(col).toString());
            }
            list.add(rowMap);
        }
        return list;
    }

    /**
     * 顺序解析
     *
     * @param sheet     Sheet
     * @param firstLine 起始行
     * @param cols      总列数
     * @return Sheet内容
     * @author July july_sky@foxmail.com
     * @date 2018-03-09 15:50:19
     * @Copyright ©2015-2035 湘豫(北京)科技有限公司. All Rights Reserved.
     * @version 1.0
     */
    public static List<Map<Integer, String>> getSheet(Sheet sheet, int firstLine, int cols) {
        int rowCount = sheet.getLastRowNum();
        List<Map<Integer, String>> list = new ArrayList<>();
        if (rowCount <= firstLine + 1) {
            return list;
        }
        Map<Integer, String> rowMap = null;
        for (int row = firstLine; row < rowCount; row++) {
            rowMap = new HashMap<>();
            for (int col = 0; col < cols; col++) {
                rowMap.put(col, StringUtils.isBlank(sheet.getRow(row).getCell(col)) ? sheet.getRow(row).getCell(col).toString() : "");
            }
            list.add(rowMap);
        }
        return list;
    }

    /**
     * 获取Sheet内容(2013版)
     *
     * @param inputStream Excel文件流
     * @param sheetIndex  Sheet索引
     * @param firstLine   起始行
     * @param cols        总列数
     * @return Sheet内容
     * @author July july_sky@foxmail.com
     * @date 2018-03-09 15:57:54
     * @Copyright ©2015-2035 湘豫(北京)科技有限公司. All Rights Reserved.
     * @version 1.0
     */
    public static List<Map<String, String>> getSheetXls(InputStream inputStream, int sheetIndex, int firstLine,
      int cols) throws FileNotFoundException, InvalidFormatException, IOException {
        return getSheetTitle(getWorkbookXls(inputStream), sheetIndex, firstLine, cols);
    }

    /**
     * 获取Sheet内容(2017版)
     *
     * @param inputStream Excel文件流
     * @param sheetIndex  Sheet索引
     * @param firstLine   起始行
     * @param cols        总列数
     * @return Sheet内容
     * @author July july_sky@foxmail.com
     * @date 2018-03-09 15:57:54
     * @Copyright ©2015-2035 湘豫(北京)科技有限公司. All Rights Reserved.
     * @version 1.0
     */
    public static List<Map<String, String>> getSheetXlsx(InputStream inputStream, int sheetIndex, int firstLine,
      int cols) throws FileNotFoundException, InvalidFormatException, IOException {
        return getSheetTitle(getWorkbookXlsx(inputStream), sheetIndex, firstLine, cols);
    }
}
